﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class SupplierCustomerService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlertService m_alertService;
        asuheaderService m_asuheaderService;
        acuheaderService m_acuheaderService;

        public SupplierCustomerService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
            m_alertService = new AlertService();
            m_asuheaderService = new asuheaderService();
            m_acuheaderService = new acuheaderService();
        }
        public SupplierCustomerService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_alertService = new AlertService();
            m_asuheaderService = new asuheaderService();
            m_acuheaderService = new acuheaderService();
        }
        public IList GetAllSupplierCustomerMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetAllSupplierCustomerMapping(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new SupplierCustomer(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public bool IsValidSupplierMapping(string suppcustName)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //m_dataBaseConectivity.AgressoSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetSupplierCustomerMappingByName(), 
                    m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppcustName;
                SqlDataReader rd = cmd.ExecuteReader();
                SupplierCustomer suppCust = null;
                if (rd.HasRows)
                {
                    rd.Read();
                    suppCust = new SupplierCustomer(false, rd);
                }
                rd.Close();
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (suppCust == null)
                {
                    cmd.CommandText = SupplierCustomerSQL.GetSoftmarSupplierCustomerByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppcustName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        suppCust = new SupplierCustomer(true, rd);
                    }
                    else
                    {
                        throw new Exception("Can not Find Supplier in Softmar Organisation: " + suppcustName);
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (suppCust != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = SupplierCustomerSQL.InsertSupplierCustomerMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = suppCust.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = suppCust.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = suppCust.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = suppCust.co_type;
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = suppCust.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = suppCust.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppCust.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = suppCust.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppCust.short_name;
                        cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                        cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                        cmd.Parameters.Add("@agr_cust_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_cust_rel_value;
                        cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                        cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_profitcentre_dim_value;
                        cmd.Parameters.Add("@cost_centre_chartering", SqlDbType.NVarChar).Value = suppCust.cost_centre_chartering;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                        cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                        cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                        
                        cmd.ExecuteNonQuery();
                    }
                }
                return suppCust == null ? false : suppCust.agr_supp_apar_id != string.Empty && suppCust.agr_supp_apar_name != string.Empty;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return false;
        }
        public SupplierCustomer GetSupplierMapping(string suppcustName)
        {
            SupplierCustomer suppCust = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetSupplierCustomerMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppcustName;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    suppCust = new SupplierCustomer(false, rd);
                }
                rd.Close();
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (suppCust == null)
                {
                    cmd.CommandText = SupplierCustomerSQL.GetSoftmarSupplierCustomerByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppcustName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        suppCust = new SupplierCustomer(true, rd);
                    }
                    else
                    {
                       // throw new Exception("Can not Find Supplier in Softmar Organisation: " + suppcustName);
                        suppCust = new SupplierCustomer();
                        suppCust.full_name = suppcustName;
                        suppCust.short_name = suppcustName;
                        suppCust.account_code = "Can not Find Supplier in Softmar Master";
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (suppCust != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = SupplierCustomerSQL.InsertSupplierCustomerMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = suppCust.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = suppCust.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = suppCust.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = suppCust.co_type;
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = suppCust.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = suppCust.full_address.Length > 50 ? suppCust.full_address.Substring(0, 50) : suppCust.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppCust.full_name.Length > 50 ? suppCust.full_name.Substring(0, 50) : suppCust.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = suppCust.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppCust.short_name;
                        cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                        cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                        cmd.Parameters.Add("@agr_cust_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_cust_rel_value;
                        cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                        cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_profitcentre_dim_value;
                        cmd.Parameters.Add("@cost_centre_chartering", SqlDbType.NVarChar).Value = suppCust.cost_centre_chartering;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                        cmd.Parameters.Add("@interco", SqlDbType.NVarChar).Value = suppCust.interco;
                        cmd.Parameters.Add("@sup_account", SqlDbType.NVarChar).Value = suppCust.sup_account;
                        cmd.Parameters.Add("@cus_account", SqlDbType.NVarChar).Value = suppCust.cus_account;
                        cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                        cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                        cmd.ExecuteNonQuery();
                    }
                }
                if (suppCust.agr_supp_apar_id == "")
                {
                    asuheader asuH = m_asuheaderService.GetSupplierByName(suppCust.full_name);
                    if (asuH != null)
                    {
                        suppCust.agr_supp_apar_id = asuH.apar_id;
                        suppCust.agr_supp_apar_name = asuH.apar_name;
                        //suppCust.agr_supp_rel_value = agrProfitCentre.rel_value;
                        suppCust.client = asuH.client;
                        cmd.CommandText = SupplierCustomerSQL.UpdateSupplierMappingAgresso();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                        cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_rel_value;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                        cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;
                        cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        // search base on user description manual entry 
                        if (suppCust.agr_supp_apar_name != string.Empty)
                        {
                            asuH = m_asuheaderService.GetSupplierByName(suppCust.agr_supp_apar_name);
                            if (asuH != null)
                            {
                                suppCust.agr_supp_apar_id = asuH.apar_id;
                                suppCust.agr_supp_apar_name = asuH.apar_name;
                                //suppCust.agr_supp_rel_value = agrProfitCentre.rel_value;
                                suppCust.client = asuH.client;
                                cmd.CommandText = SupplierCustomerSQL.UpdateSupplierMappingAgresso();
                                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                                cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                                //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_rel_value;
                                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                                cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;
                                cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                                cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                if (suppCust.agr_supp_apar_id != "")
                {
                    asuheader asuH = m_asuheaderService.GetSupplierByCode(suppCust.agr_supp_apar_id);
                    if(asuH != null)
                        suppCust.country_code_sup = asuH.country_code;
                }
                return suppCust;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            //return suppCust;
        }
        public SupplierCustomer GetCustomerMapping(string suppcustName)
        {
            SupplierCustomer suppCust = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetSupplierCustomerMappingByName(),
                    m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppcustName;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    suppCust = new SupplierCustomer(false, rd);
                }
                rd.Close();
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (suppCust == null)
                {
                    cmd.CommandText = SupplierCustomerSQL.GetSoftmarSupplierCustomerByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppcustName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        suppCust = new SupplierCustomer(true, rd);
                    }
                    else
                    {
                        // throw new Exception("Can not Find Supplier in Softmar Organisation: " + suppcustName);
                        suppCust = new SupplierCustomer();
                        suppCust.full_name = suppcustName;
                        suppCust.short_name = suppcustName;
                        suppCust.account_code = "Can not Find Customer in Softmar Master";
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (suppCust != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = SupplierCustomerSQL.InsertSupplierCustomerMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = suppCust.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = suppCust.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = suppCust.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = suppCust.co_type.Trim();
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = suppCust.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = suppCust.full_address.Length > 50 ? suppCust.full_address.Substring(0, 50) : suppCust.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppCust.full_name.Length > 50 ? suppCust.full_name.Substring(0, 50) : suppCust.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = suppCust.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppCust.short_name.Length > 50 ? suppCust.short_name.Substring(0, 50) : suppCust.short_name;
                        cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                        cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                        cmd.Parameters.Add("@agr_cust_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_cust_rel_value;
                        cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                        cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_profitcentre_dim_value;
                        cmd.Parameters.Add("@cost_centre_chartering", SqlDbType.NVarChar).Value = suppCust.cost_centre_chartering;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                        cmd.Parameters.Add("@interco", SqlDbType.NVarChar).Value = suppCust.interco;
                        cmd.Parameters.Add("@sup_account", SqlDbType.NVarChar).Value = suppCust.sup_account;
                        cmd.Parameters.Add("@cus_account", SqlDbType.NVarChar).Value = suppCust.cus_account;
                        cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                        cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                        cmd.ExecuteNonQuery();
                    }
                }
                if (suppCust.agr_cust_apar_id == "")
                {
                    acuheader acuH = m_acuheaderService.GetCustomerByName(suppCust.full_name);
                    if (acuH != null)
                    {
                        suppCust.agr_cust_apar_id = acuH.apar_id;
                        suppCust.agr_cust_apar_name = acuH.apar_name;
                        //suppCust.agr_supp_rel_value = agrProfitCentre.rel_value;
                        suppCust.cust_client = acuH.client;
                        cmd.CommandText = SupplierCustomerSQL.UpdateCustomerMappingAgresso();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                        cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                        cmd.Parameters.Add("@cust_client", SqlDbType.NVarChar).Value = suppCust.cust_client;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_rel_value;
                        cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;
                        cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                    //else
                    //{
                    //    // search base on user description manual entry 
                    //    if (suppCust.agr_cust_apar_name.Trim() != string.Empty)
                    //    {
                    //        acuH = m_acuheaderService.GetCustomerByName(suppCust.agr_cust_apar_name);
                    //        if (acuH != null)
                    //        {
                    //            suppCust.agr_cust_apar_id = acuH.apar_id;
                    //            suppCust.agr_cust_apar_name = acuH.apar_name;
                    //            //suppCust.agr_supp_rel_value = agrProfitCentre.rel_value;
                    //            suppCust.cust_client = acuH.client;
                    //            cmd.CommandText = SupplierCustomerSQL.UpdateCustomerMappingAgresso();
                    //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    //            cmd.Parameters.Clear();
                    //            cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                    //            cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                    //            cmd.Parameters.Add("@cust_client", SqlDbType.NVarChar).Value = suppCust.cust_client;
                    //            //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_rel_value;
                    //            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    //            cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;
                    //            cmd.ExecuteNonQuery();
                    //        }
                    //    }
                    //}
                }
               
                return suppCust;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            //return suppCust;
        }
        public bool AlertIfNotValidSupplier(SupplierCustomer suppCust, string key, AlertType alertType)
        {
            bool valid = suppCust.IsValidSupplier();
            if (valid)
                m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.RESOLVED, "Please update Agresso map for Supplier with name : " + suppCust.full_name, "Supplier");
            else
                m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.PENDING, "Please update Agresso map for Supplier with name : " + suppCust.full_name, "Supplier");
            return valid;
        }
        public bool IsValidCustomerMapping(string suppcustName)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //m_dataBaseConectivity.AgressoSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetSupplierCustomerMappingByName(),
                    m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppcustName;
                SqlDataReader rd = cmd.ExecuteReader();
                SupplierCustomer suppCust = null;
                if (rd.HasRows)
                {
                    rd.Read();
                    suppCust = new SupplierCustomer(false, rd);
                }
                rd.Close();
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (suppCust == null)
                {
                    cmd.CommandText = SupplierCustomerSQL.GetSoftmarSupplierCustomerByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppcustName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        suppCust = new SupplierCustomer(true, rd);

                    }
                    else
                    {
                       // throw new Exception("Can not Find Supplier in Softmar Organisation: " + suppcustName);
                        suppCust = new SupplierCustomer();
                        suppCust.full_name = suppcustName;
                        suppCust.short_name = suppcustName;
                        suppCust.account_code = "Can not Find Supplier in Softmar Master";
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (suppCust != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = SupplierCustomerSQL.InsertSupplierCustomerMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = suppCust.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = suppCust.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = suppCust.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = suppCust.co_type;
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = suppCust.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = suppCust.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppCust.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = suppCust.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppCust.short_name;
                        cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                        cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                        cmd.Parameters.Add("@agr_cust_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_cust_rel_value;
                        cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                        cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_profitcentre_dim_value;
                        cmd.Parameters.Add("@cost_centre_chartering", SqlDbType.NVarChar).Value = suppCust.cost_centre_chartering;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                        cmd.Parameters.Add("@interco", SqlDbType.NVarChar).Value = suppCust.interco;
                        cmd.Parameters.Add("@sup_account", SqlDbType.NVarChar).Value = suppCust.sup_account;
                        cmd.Parameters.Add("@cus_account", SqlDbType.NVarChar).Value = suppCust.cus_account;
                        cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                        cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                        cmd.ExecuteNonQuery();
                    }
                }
                return suppCust == null ? false : suppCust.agr_cust_apar_id != string.Empty && suppCust.agr_cust_apar_name != string.Empty;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return false;
        }
        public SupplierCustomer IsValidCustomerMapping(string suppcustName, string key, AlertType alertType)
        {
            SupplierCustomer suppCust = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetSupplierCustomerMappingByName(),
                    m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppcustName;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    suppCust = new SupplierCustomer(false, rd);
                }
                rd.Close();
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                if (suppCust == null)
                {
                    cmd.CommandText = SupplierCustomerSQL.GetSoftmarSupplierCustomerByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppcustName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        suppCust = new SupplierCustomer(true, rd);
                    }
                    else
                    {
                        throw new Exception("Can not Find Customer in Softmar Organisation: " + suppcustName);
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (suppCust != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = SupplierCustomerSQL.InsertSupplierCustomerMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = suppCust.account_code;
                        cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = suppCust.active;
                        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = suppCust.city;
                        cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = suppCust.co_type;
                        cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = suppCust.country;
                        cmd.Parameters.Add("@created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = suppCust.full_address;
                        cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppCust.full_name;
                        cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = suppCust.short_code;
                        cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppCust.short_name;
                        cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                        cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                        cmd.Parameters.Add("@agr_cust_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_cust_rel_value;
                        cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                        cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                        //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_profitcentre_dim_value;
                        cmd.Parameters.Add("@interco", SqlDbType.NVarChar).Value = suppCust.interco;
                        cmd.Parameters.Add("@sup_account", SqlDbType.NVarChar).Value = suppCust.sup_account;
                        cmd.Parameters.Add("@cus_account", SqlDbType.NVarChar).Value = suppCust.cus_account;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                        cmd.Parameters.Add("@cost_centre_chartering", SqlDbType.NVarChar).Value = suppCust.cost_centre_chartering;
                        cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                        cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                        cmd.ExecuteNonQuery();
                    }
                }
                acuheader acuH = m_acuheaderService.GetCustomerByName(suppCust.full_name);
                if (acuH != null)
                {
                    suppCust.agr_supp_apar_id = acuH.apar_id;
                    suppCust.agr_supp_apar_name = acuH.apar_name;
                    //suppCust.agr_supp_rel_value = agrProfitCentre.rel_value;
                    suppCust.client = acuH.client;
                    cmd.CommandText = SupplierCustomerSQL.UpdateCustomerMappingAgresso();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                    cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                    //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_rel_value;
                    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                    cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;
                    cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                    cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    // search base on user description manual entry 
                    if (suppCust.agr_supp_apar_name != string.Empty)
                    {
                        acuH = m_acuheaderService.GetCustomerByName(suppCust.full_name);
                        if (acuH != null)
                        {
                            suppCust.agr_supp_apar_id = acuH.apar_id;
                            suppCust.agr_supp_apar_name = acuH.apar_name;
                            //suppCust.agr_supp_rel_value = agrProfitCentre.rel_value;
                            suppCust.client = acuH.client;
                            cmd.CommandText = SupplierCustomerSQL.UpdateCustomerMappingAgresso();
                            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                            cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name;
                            //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_rel_value;
                            cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                            cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;
                            cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                if (suppCust.IsValidCustomer())
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.RESOLVED, "Please update Agresso map for Customer with name : " + suppcustName, "Customer");
                else
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.PENDING, "Please update Agresso map for Customer with name : " + suppcustName, "Customer");
                return suppCust;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return suppCust;
        }
        public void UpdateSupplierCustomerMapping(SupplierCustomer suppCust)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = SupplierCustomerSQL.UpdateSupplierCustomerMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = suppCust.supp_cust_id;

                cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = suppCust.account_code;
                cmd.Parameters.Add("@active", SqlDbType.NVarChar).Value = suppCust.active;
                cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = suppCust.city;
                cmd.Parameters.Add("@co_type", SqlDbType.NVarChar).Value = suppCust.co_type;
                cmd.Parameters.Add("@country", SqlDbType.NVarChar).Value = suppCust.country;
                cmd.Parameters.Add("@full_address", SqlDbType.NVarChar).Value = suppCust.full_address;
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = suppCust.full_name;
                cmd.Parameters.Add("@short_code", SqlDbType.NVarChar).Value = suppCust.short_code;
                cmd.Parameters.Add("@short_name", SqlDbType.NVarChar).Value = suppCust.short_name;

                cmd.Parameters.Add("@agr_cust_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_id;
                cmd.Parameters.Add("@agr_cust_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_cust_apar_name;
                cmd.Parameters.Add("@agr_cust_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_cust_rel_value;

                cmd.Parameters.Add("@agr_supp_apar_id", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_id;
                cmd.Parameters.Add("@agr_supp_apar_name", SqlDbType.NVarChar).Value = suppCust.agr_supp_apar_name.Length > 50 ? suppCust.agr_supp_apar_name.Substring(0, 50) : suppCust.agr_supp_apar_name;
                //cmd.Parameters.Add("@agr_supp_rel_value", SqlDbType.NVarChar).Value = suppCust.agr_supp_profitcentre_dim_value;
                cmd.Parameters.Add("@interco", SqlDbType.NVarChar).Value = suppCust.interco;
                cmd.Parameters.Add("@sup_account", SqlDbType.NVarChar).Value = suppCust.sup_account;
                cmd.Parameters.Add("@cus_account", SqlDbType.NVarChar).Value = suppCust.cus_account;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = suppCust.client;
                cmd.Parameters.Add("@cost_centre_chartering", SqlDbType.NVarChar).Value = suppCust.cost_centre_chartering;
                //cmd.Parameters.Add("@legal_entity_name", SqlDbType.NVarChar).Value = suppCust.legal_entity_name;
                cmd.Parameters.Add("@interco_apar_id", SqlDbType.NVarChar).Value = suppCust.interco_apar_id;
                cmd.Parameters.Add("@cust_client", SqlDbType.NVarChar).Value = suppCust.cust_client;
                cmd.Parameters.Add("@apar_gr_id", SqlDbType.NVarChar).Value = suppCust.apar_gr_id;
                cmd.Parameters.Add("@cus_apar_gr_id", SqlDbType.NVarChar).Value = suppCust.cus_apar_gr_id;

                cmd.Parameters.Add("@legal_entity_id", SqlDbType.Int).Value = suppCust.ProfitCentre.profit_centre_id;
                cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;

                cmd.ExecuteNonQuery();

            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllSupplierCustomerMapping(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetAllSupplierCustomerMapping(p), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new SupplierCustomer(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public SupplierCustomer GetSupplierMappingByID(long id)
        {
            SupplierCustomer result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetAllSupplierCustomerMappingByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@supp_cust_id", SqlDbType.Int).Value = id;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result = new SupplierCustomer(false, rd);
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public SupplierCustomer GetSupplierMappingByFullName(string full_name)
        {
            SupplierCustomer result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(SupplierCustomerSQL.GetSupplierMappingByFullName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar).Value = full_name;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result = new SupplierCustomer(false, rd);
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
    }
}
